package xju.rjxy.shop.mapper.impl;

import xju.rjxy.shop.mapper.FriendMapper;
import xju.rjxy.shop.model.domain.Friend;
import xju.rjxy.shop.model.vo.FriendVO;
import xju.rjxy.shop.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 好友数据访问接口实现类
 */
public class FriendMapperImpl implements FriendMapper {
    private final QueryRunner runner = new QueryRunner();

    /**
     * 查询好友列表
     */
    @Override
    public List<FriendVO> selectFriendList() {
        // 修正表名t_friend为friends，并完善字段
        String sql = "SELECT " +
                "id, " +
                "user_id AS userId, " +
                "friend_id AS friendId, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode " +
                "FROM friends " +
                "WHERE flag = 2"; // 过滤已同意的好友关系

        List<FriendVO> friendList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            friendList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return friendList;
    }
    
    /**
     * 根据用户ID查询好友列表
     */
    @Override
    public List<FriendVO> selectByUserId(Integer userId) {
        String sql = "SELECT " +
                "f.id, " +
                "f.user_id AS userId, " +
                "f.friend_id AS friendId, " +
                "f.create_time AS createTime, " +
                "f.flag, " +
                "f.status_code AS statusCode, " +
                "f.tag, " +
                "u.username AS friendUsername, " +
                "u.avatar AS friendAvatar " +
                "FROM friends f " +
                "JOIN users u ON f.friend_id = u.id " +
                "WHERE f.user_id = ? AND f.flag = 2 AND u.deleted = 0";

        List<FriendVO> friendList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            friendList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class),
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return friendList;
    }
    
    /**
     * 根据分组查询好友列表
     */
    @Override
    public List<FriendVO> selectByTag(Integer userId, String tag) {
        String sql = "SELECT " +
                "f.id, " +
                "f.user_id AS userId, " +
                "f.friend_id AS friendId, " +
                "f.create_time AS createTime, " +
                "f.flag, " +
                "f.status_code AS statusCode, " +
                "f.tag, " +
                "u.username AS friendUsername, " +
                "u.avatar AS friendAvatar " +
                "FROM friends f " +
                "JOIN users u ON f.friend_id = u.id " +
                "WHERE f.user_id = ? AND f.tag = ? AND f.flag = 2 AND u.deleted = 0";

        List<FriendVO> friendList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            friendList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class),
                    userId, tag
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return friendList;
    }
    
    /**
     * 删除好友
     */
    @Override
    public int deleteFriend(Integer userId, Integer friendId) {
        String sql = "DELETE FROM friends WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, userId, friendId, friendId, userId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 添加好友
     */
    @Override
    public int insertFriend(Friend friend) {
        String sql = "INSERT INTO friends (user_id, friend_id, create_time, flag, status_code, tag) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        System.out.println("[Mapper.insertFriend] SQL: " + sql);
        System.out.println("[Mapper.insertFriend] 参数: userId=" + friend.getUserId() + ", friendId=" + friend.getFriendId() + ", createTime=" + friend.getCreateTime() + ", flag=" + friend.getFlag() + ", statusCode=" + friend.getStatusCode() + ", tag=" + friend.getTag());
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    friend.getUserId(), 
                    friend.getFriendId(), 
                    friend.getCreateTime(), 
                    friend.getFlag(), 
                    friend.getStatusCode(), 
                    friend.getTag());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        System.out.println("[Mapper.insertFriend] 返回: " + result);
        return result;
    }
    
    /**
     * 更新好友分组
     */
    @Override
    public int updateFriendTag(Integer userId, Integer friendId, String tag) {
        String sql = "UPDATE friends SET tag = ? WHERE user_id = ? AND friend_id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, tag, userId, friendId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }

    /**
     * 更新好友状态
     */
    @Override
    public int updateFriendStatus(Integer userId, Integer friendId, Integer flag, String statusCode) {
        String sql = "UPDATE friends SET flag = ?, status_code = ? WHERE user_id = ? AND friend_id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, flag, statusCode, userId, friendId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }

    /**
     * 查询单个好友详情
     */
    @Override
    public FriendVO selectFriendInfo(Integer userId, Integer friendId) {
        String sql = "SELECT " +
                "f.id, " +
                "f.user_id AS userId, " +
                "f.friend_id AS friendId, " +
                "f.create_time AS createTime, " +
                "f.flag, " +
                "f.status_code AS statusCode, " +
                "f.tag, " +
                "u.username AS friendUsername, " +
                "u.avatar AS friendAvatar " +
                "FROM friends f " +
                "JOIN users u ON f.friend_id = u.id " +
                "WHERE f.user_id = ? AND f.friend_id = ? AND f.flag = 2 AND u.deleted = 0";
        FriendVO friendVO = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            friendVO = runner.query(connection, sql, new org.apache.commons.dbutils.handlers.BeanHandler<>(FriendVO.class), userId, friendId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return friendVO;
    }

    @Override
    public List<FriendVO> selectPendingRequestList(Integer userId) {
        String sql = "SELECT t.id, t.user_id AS userId, t.friend_id AS friendId, t.create_time AS createTime, t.flag, t.status_code AS statusCode, t.tag, t.friendUsername, t.friendAvatar, t.friendEmail " +
                "FROM ( " +
                "  SELECT f.*, u.username AS friendUsername, u.avatar AS friendAvatar, u.email AS friendEmail, " +
                "         LEAST(f.user_id, f.friend_id) AS uid1, GREATEST(f.user_id, f.friend_id) AS uid2 " +
                "  FROM friends f " +
                "  JOIN users u ON u.id = f.user_id " +
                "  WHERE f.friend_id = ? AND f.flag = 1 AND f.status_code = 'PENDING' " +
                ") t " +
                "INNER JOIN ( " +
                "  SELECT LEAST(user_id, friend_id) AS uid1, GREATEST(user_id, friend_id) AS uid2, MAX(create_time) AS max_time " +
                "  FROM friends " +
                "  WHERE flag = 1 AND status_code = 'PENDING' " +
                "  GROUP BY uid1, uid2 " +
                ") tt ON t.uid1 = tt.uid1 AND t.uid2 = tt.uid2 AND t.create_time = tt.max_time";
        List<FriendVO> requestList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            requestList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class),
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return requestList;
    }

    @Override
    public List<FriendVO> selectAllFriendsWithPending(Integer userId) {
        String sql = "SELECT t.id, t.otherId, t.createTime, t.flag, t.statusCode, t.tag, t.otherUsername, t.otherAvatar, t.otherEmail " +
                "FROM ( " +
                "  SELECT f.id, " +
                "         CASE WHEN f.user_id = ? THEN f.friend_id ELSE f.user_id END AS otherId, " +
                "         f.create_time AS createTime, f.flag, f.status_code AS statusCode, f.tag, " +
                "         u.username AS otherUsername, u.avatar AS otherAvatar, u.email AS otherEmail, " +
                "         LEAST(f.user_id, f.friend_id) AS uid1, GREATEST(f.user_id, f.friend_id) AS uid2 " +
                "  FROM friends f " +
                "  JOIN users u ON u.id = (CASE WHEN f.user_id = ? THEN f.friend_id ELSE f.user_id END) " +
                "  WHERE (f.user_id = ? OR f.friend_id = ?) AND f.flag = 2 " +
                ") t " +
                "INNER JOIN ( " +
                "  SELECT LEAST(user_id, friend_id) AS uid1, GREATEST(user_id, friend_id) AS uid2, MAX(create_time) AS max_time " +
                "  FROM friends " +
                "  WHERE flag = 2 " +
                "  GROUP BY uid1, uid2 " +
                ") tt ON t.uid1 = tt.uid1 AND t.uid2 = tt.uid2 AND t.createTime = tt.max_time";
        List<FriendVO> list = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            list = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class),
                    userId, userId, userId, userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return list;
    }

    @Override
    public List<FriendVO> selectAllFriendsWithPendingPage(Integer userId, int offset, int limit) {
        String sql = "SELECT t.id, t.otherId, t.createTime, t.flag, t.statusCode, t.tag, t.otherUsername, t.otherAvatar, t.otherEmail " +
                "FROM ( " +
                "  SELECT f.id, " +
                "         CASE WHEN f.user_id = ? THEN f.friend_id ELSE f.user_id END AS otherId, " +
                "         f.create_time AS createTime, f.flag, f.status_code AS statusCode, f.tag, " +
                "         u.username AS otherUsername, u.avatar AS otherAvatar, u.email AS otherEmail, " +
                "         LEAST(f.user_id, f.friend_id) AS uid1, GREATEST(f.user_id, f.friend_id) AS uid2 " +
                "  FROM friends f " +
                "  JOIN users u ON u.id = (CASE WHEN f.user_id = ? THEN f.friend_id ELSE f.user_id END) " +
                "  WHERE (f.user_id = ? OR f.friend_id = ?) AND f.flag = 2 " +
                ") t " +
                "INNER JOIN ( " +
                "  SELECT LEAST(user_id, friend_id) AS uid1, GREATEST(user_id, friend_id) AS uid2, MAX(create_time) AS max_time " +
                "  FROM friends " +
                "  WHERE flag = 2 " +
                "  GROUP BY uid1, uid2 " +
                ") tt ON t.uid1 = tt.uid1 AND t.uid2 = tt.uid2 AND t.createTime = tt.max_time " +
                "LIMIT ?, ?";
        List<FriendVO> list = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            list = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(FriendVO.class),
                    userId, userId, userId, userId, offset, limit
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return list;
    }

    @Override
    public int selectAllFriendsWithPendingTotal(Integer userId) {
        String sql = "SELECT COUNT(*) FROM ( " +
                "  SELECT 1 " +
                "  FROM friends f " +
                "  WHERE (f.user_id = ? OR f.friend_id = ?) AND f.flag = 2 " +
                "  GROUP BY LEAST(f.user_id, f.friend_id), GREATEST(f.user_id, f.friend_id) " +
                ") t";
        int total = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            total = runner.query(connection, sql, rs -> rs.next() ? rs.getInt(1) : 0, userId, userId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return total;
    }
} 